using System;
using System.Collections.Generic;
using System.Text;
using System.Threading;
using System.Diagnostics;
using System.Data.SqlClient;
using System.Data;
using System.Net;
using PSLibrary = Microsoft.Office.Project.Server.Library;
using Microsoft.Office.Project.EPMSync.Config.Library;
using System.Reflection;
using System.Globalization;
using System.Web.Services.Protocols;

namespace Microsoft.Office.Project.EPMSync.Service
{
    class SyncThread
    {
        private SyncConfigFile _configFile;
        private SyncConfig _config;
        private SqlConnection _conn;

        private CustomFieldCollection _projectCustomFieldList;
        private AttributeChangeCollection _attributeChanges;

        private ProjectWS.Project _projWebSvc;
        private QueueSystemWS.QueueSystem _queueWebSvc;
        private CustomFieldsWS.CustomFields _customFieldsWebSvc;

        private Tracing _tracing = null;

        private ManualResetEvent _pause;

        private GetSyncNowCallback _getSyncNow;
        private SetSyncNowCallback _setSyncNow;

        /// <summary>
        /// Constructor
        /// </summary>
        /// <param name="tracing">Pointer to the active tracing component.</param>
        /// <param name="pause">Allows for the service to tell the thread to wait for instructions.</param>
        /// <param name="getSyncNow">Callback location for allowing the service to tell the thread to sync now.</param>
        /// <param name="setSyncNow">Callback location for allowing the service to tell the thread to sync now.</param>
        public SyncThread(Tracing tracing, ManualResetEvent pause, GetSyncNowCallback getSyncNow, SetSyncNowCallback setSyncNow)
        {
            _tracing = tracing;
            _pause = pause;
            _getSyncNow = getSyncNow;
            _setSyncNow = setSyncNow;
        }


        #region Initialization Methods

        private void InitializeWebServices(string serverUrl)
        {
            _projWebSvc = new ProjectWS.Project();
            _projWebSvc.CookieContainer = new CookieContainer();
            _projWebSvc.Credentials = CredentialCache.DefaultCredentials;
            _projWebSvc.Url = WebServiceUrl.Project(serverUrl);

            _queueWebSvc = new QueueSystemWS.QueueSystem();
            _queueWebSvc.CookieContainer = _projWebSvc.CookieContainer;
            _queueWebSvc.Credentials = CredentialCache.DefaultCredentials;
            _queueWebSvc.Url = WebServiceUrl.QueueSystem(serverUrl);

            _customFieldsWebSvc = new CustomFieldsWS.CustomFields();
            _customFieldsWebSvc.CookieContainer = _projWebSvc.CookieContainer;
            _customFieldsWebSvc.Credentials = CredentialCache.DefaultCredentials;
            _customFieldsWebSvc.Url = WebServiceUrl.CustomFields(serverUrl);
        }


        private bool SystemRunning(int timeout)
        {
            DateTime timeoutTime = DateTime.Now.AddSeconds(timeout);
            _tracing.WriteTrace(_tracing.MainSwitch.TraceInfo, "Entering check for Project Server / Portfolio Server running.");

            do
            {
                if (DateTime.Now > timeoutTime)
                {
                    _tracing.WriteTrace(_tracing.MainSwitch.TraceError, "Timeout waiting for Project Server / Portfolio Server to run.");
                    return false;
                }

                if (VerifyPWA())
                {
                    _tracing.WriteTrace(_tracing.MainSwitch.TraceInfo, "Project Server / Portfolio Server is running.");
                    return true;
                }

                // Wait before cycling.
                Thread.Sleep(5000);
                // Block if the service is paused or is shutting down.
                //_pause.WaitOne();
            } while (true);

        }

        private bool VerifyPWA()
        {
            try
            {
                // Create a request instance.
                WebRequest myRequest = WebRequest.Create(_config.ProjectServerURL);
                myRequest.Credentials = CredentialCache.DefaultCredentials;

                // Get the response.
                WebResponse myResponse = myRequest.GetResponse();
                myResponse.Close();
                return true;
            }
            catch (WebException)
            {
                return false;
            }

        }

        private bool InitializeData()
        {
            // Read configuration file
            Assembly epmAssembly = Assembly.GetExecutingAssembly();
            string fullPath = epmAssembly.Location;
            string directory = fullPath.Substring(0, fullPath.LastIndexOf(@"\") + 1);

            _configFile = new SyncConfigFile(_tracing, directory + "EPMSync.config");
            _config = _configFile.SyncConfiguration;
            if (_config == null)
                return false;

            if (!SystemRunning(300))
                return false;

            try
            {
                // Create SQL Connection
                _conn = new SqlConnection(_config.PortfolioServerDatabaseConnectionString);
                _conn.Open();
                if (_conn.State != System.Data.ConnectionState.Open)
                    return false;

                // Verify the necessary DB objects exist, create them if they are not there.
                if (!VerifyDBObjectsExist())
                {
                    _tracing.WriteTrace(_tracing.MainSwitch.TraceError, "The database objects and EPMSync objects versions are inconsistent.  Please reinstall the EPMSync Tool");
                    return false;
                }
                // Create connection to Project Server, get initial data read
                InitializeWebServices(_config.ProjectServerURL);

                _projectCustomFieldList = new CustomFieldCollection(_tracing, _customFieldsWebSvc, _config);
                if (!_projectCustomFieldList.Initialize())
                {
                    _tracing.WriteTrace(_tracing.MainSwitch.TraceError, "Unable to read the list of Project Server custom fields.  Please fix the problem and restart the service.");
                    return false;
                }

                if (!_projectCustomFieldList.MappedCustomFieldsExist())
                {
                    _tracing.WriteTrace(_tracing.MainSwitch.TraceError, "Unable to find the correct custom field to attribute mapping.  Please fix the problem and restart the service.");
                    return false;
                }

                _attributeChanges = new AttributeChangeCollection(_conn, _tracing, _config, _projWebSvc);
                if (!_attributeChanges.Initialize())
                {
                    return false;
                }

            }
            catch (SqlException sqlEx)
            {
                _tracing.WriteTrace(_tracing.MainSwitch.TraceError, String.Format(CultureInfo.InvariantCulture, "A SQL exception occured connecting to the PPS database.  Exception: {0}", sqlEx.Message));
                return false;
            }
            catch (Exception ex)
            {
                _tracing.WriteTrace(_tracing.MainSwitch.TraceError, String.Format(CultureInfo.InvariantCulture, "An exception occured initializing the service.  Exception: {0}", ex.Message));
                return false;
            }
            return true;
        }

        private bool VerifyDBObjectsExist()
        {
            DatabaseSetup dbs = new DatabaseSetup(_conn, _tracing);
            return dbs.VerifyDBObjects();
        }

        private void ReleaseData()
        {
            // Destroy SQL Connection
            _conn.Close();

        }

        #endregion

        #region Process Change Workflow

        /// <summary>
        /// Entry point for staring up the thread for process changes.  Calls the initialization methods, if they fail
        /// the worker thread will exit.
        /// </summary>
        public void WorkerMethod()
        {
            _tracing.WriteTrace(_tracing.MainSwitch.TraceInfo, "Starting the service worker thread");
            bool syncNow = false;

            _tracing.ProcessingState = SyncState.Initializing;

            // If the configuration data doesn't initialize, then terminate the thread
            if (InitializeData())
            {
                DateTime wakeupTime = DateTime.Now.AddSeconds(_config.WakeupInterval);
                _tracing.WriteTrace(_tracing.MainSwitch.TraceInfo, "Entering heartbeat cycle");
                _tracing.ProcessingState = SyncState.Idle;

                try
                {
                    do
                    {
                        syncNow = _getSyncNow();
                        if (syncNow)
                        {
                            _tracing.ProcessingState = SyncState.Processing;
                            _tracing.WriteTrace(_tracing.MainSwitch.TraceInfo, "Thread received SyncNow Signal");
                            _setSyncNow(false);
                            syncNow = false;
                            ProcessArchive(_config.PortfolioServerDatabaseConnectionString);
                            ProcessAttributeChanges();
                            wakeupTime = DateTime.Now.AddSeconds(_config.WakeupInterval);
                            _tracing.ProcessingState = SyncState.Idle;
                        }
                        else
                        {
                            if (DateTime.Now > wakeupTime)
                            {
                                _tracing.ProcessingState = SyncState.Processing;
                                _tracing.WriteTrace(_tracing.MainSwitch.TraceInfo, "Thread woke up to process changes");
                                ProcessAttributeChanges();
                                wakeupTime = DateTime.Now.AddSeconds(_config.WakeupInterval);
                                _tracing.ProcessingState = SyncState.Idle;
                            }
                        }

                        // Wait before cycling.
                        Thread.Sleep(1000);
                        // Block if the service is paused or is shutting down.
                        _pause.WaitOne();
                    } while (true);
                }
                catch (ThreadAbortException)
                {
                    _tracing.ProcessingState = SyncState.Exiting;
                    Thread.ResetAbort();
                    _tracing.WriteTrace(_tracing.MainSwitch.TraceInfo, "Thread abort signaled");
                }
                finally
                {
                    ReleaseData();

                }
            }

            _tracing.ProcessingState = SyncState.Closed;
            _tracing.WriteTrace(_tracing.MainSwitch.TraceInfo, "Exiting the service worker thread");
        }


        private void ProcessAttributeChanges()
        {
            if (_attributeChanges.Fill() > 0)
            {
                if (!ProcessDetectedChanges())
                {
                    // We'll try a second time to process the changes
                    // Reset the states to allow for a run again
                    _attributeChanges.UpdateAllProjectStates(ChangeState.Error, ChangeState.Detected);
                    if (!ProcessDetectedChanges())
                    {
                        // The update failed again, write an error to the log, would be great if there was anopther notification method
                        _tracing.WriteTrace(_tracing.MainSwitch.TraceError, "Some or all of the changes failed after two tries, check the rest of the log for more detailed information.");
                    }
                }
                _attributeChanges.RemoveCompletedChanges();
            }
        }


        private bool ProcessDetectedChanges()
        {
            Guid currentProjectID = Guid.Empty;
            Guid sessionGuid;
            bool fieldInProjectUpdated = false;
            bool completedCorrectly = true;

            _tracing.WriteTrace(_tracing.MainSwitch.TraceInfo, "Processing detected changes");

            do
            {
                fieldInProjectUpdated = false;

                currentProjectID = _attributeChanges.GetNextDetectedProject;
                if (currentProjectID == Guid.Empty)
                {
                    // Project doesn't exist in Project Server, move items to archive table for later processing
                    _attributeChanges.UpdateProjectState(currentProjectID, ChangeState.Detected, ChangeState.MoveToArchive);
                }
                else
                {
                    _attributeChanges.UpdateProjectState(currentProjectID, ChangeState.Detected, ChangeState.Pending);

                    sessionGuid = Guid.NewGuid();
                    ProjectWS.ProjectDataSet projectDS = ReadProject(currentProjectID, sessionGuid);

                    if (projectDS != null)
                    {
                        foreach (AttributeChange ac in _attributeChanges)
                        {
                            if ((ac.ProjectUid == currentProjectID) &&
                                (ac.ChangeStatus == ChangeState.Pending))
                            {
                                if ((_config.AttributeIsMapped(ac.AttributeId)) &&
                                    (ac.NewValue != ac.OldValue))
                                {
                                    if (UpdateField(ref projectDS, ac))
                                    {
                                        fieldInProjectUpdated = true;
                                        ac.ChangeStatus = ChangeState.SentToProjectSever;
                                    }
                                    else
                                        ac.ChangeStatus = ChangeState.Error;

                                }
                                else
                                {
                                    ac.ChangeStatus = ChangeState.PSUpdateComplete;
                                    _tracing.WriteTrace(_tracing.MainSwitch.TraceInfo, String.Format(CultureInfo.InvariantCulture, "Attribute ID {0} is not mapped, update skipped", ac.AttributeId));
                                }
                            }
                        } // for

                        // Make sure at least one field was changed in the project
                        if (fieldInProjectUpdated)
                        {
                            bool jobSuccess = UpdateProjectServerProject(sessionGuid, currentProjectID, projectDS);
                            if (completedCorrectly)
                                completedCorrectly = jobSuccess;
                        }
                        else
                        {
                            QueueCheckInProject(sessionGuid, currentProjectID);
                        }
                    }
                    else
                    {
                        // Unable to read the project from Project Server
                        _attributeChanges.UpdateProjectState(currentProjectID, ChangeState.Error);
                        _tracing.WriteTrace(_tracing.MainSwitch.TraceWarning, String.Format(CultureInfo.InvariantCulture, "Unable to read Project ID {0}, no update for this project was completed", currentProjectID));
                        completedCorrectly = false;
                    }
                }

            }
            while (_attributeChanges.ItemsDetectedNotProcessed);

            return completedCorrectly;
        }

        #endregion

        #region Process Archive

        private bool ProcessArchive(string PPSDBConnectionString)
        {
            bool successful = true;
            ProjectCollection projectList = new ProjectCollection(_tracing, _projWebSvc);
            if (!projectList.FillProjectList())
            {
                return false;
            }

            List<string> foundProjects = new List<string>();

            try
            {
                string queryString = "SELECT DISTINCT ProjectName FROM esQUEUE_ATTRIBUTE_EVENTS_ARCHIVE";
                string projectName = string.Empty;

                using (SqlConnection connection = new SqlConnection(PPSDBConnectionString))
                {
                    SqlCommand command = new SqlCommand(queryString, connection);
                    connection.Open();

                    SqlDataReader reader = command.ExecuteReader();

                    if (reader.HasRows)
                    {
                        _tracing.WriteTrace(_tracing.MainSwitch.TraceInfo, "Found changes in archive, looking for new projects");
                        while (reader.Read())
                        {
                            projectName = reader[0].ToString();
                            _tracing.WriteTrace(_tracing.MainSwitch.TraceInfo, String.Format(CultureInfo.InvariantCulture, "Comparing archive [{0}]", projectName));
                            if (projectList.Contains(ProjectCollection.FormatProjectName(projectName)))
                            {
                                _tracing.WriteTrace(_tracing.MainSwitch.TraceInfo, String.Format(CultureInfo.InvariantCulture, "Found Project match [{0}]", projectName));
                                foundProjects.Add(projectName);
                            }
                        }
                    }
                    reader.Close();
                }

                if (foundProjects.Count > 0)
                {
                    _tracing.WriteTrace(_tracing.MainSwitch.TraceInfo, String.Format(CultureInfo.InvariantCulture, "Processing archived changes, {0} changes to move found", foundProjects.Count));
                    MoveFromArchive(foundProjects, PPSDBConnectionString);
                }

            }
            catch (InvalidOperationException ioEx)
            {
                _tracing.WriteTrace(_tracing.MainSwitch.TraceError, String.Format(CultureInfo.InvariantCulture, "Connection already open, or invalid connection string.  Exception: {0}", ioEx.Message));
                successful = false;
            }
            catch (SqlException sqlEx)
            {
                _tracing.WriteTrace(_tracing.MainSwitch.TraceError, String.Format(CultureInfo.InvariantCulture, "A SQL exception occured processing the Archive.  Exception: {0}", sqlEx.Message));
                successful = false;
            }
            catch (Exception ex)
            {
                _tracing.WriteTrace(_tracing.MainSwitch.TraceError, String.Format(CultureInfo.InvariantCulture, "Processing Archive Exception: {0}", ex.Message));
                successful = false;
            }

            return successful;
        }

        private bool MoveFromArchive(List<string> foundProjects, string PPSDBConnectionString)
        {

            bool successful = true;
            string queryString = string.Empty;
            SqlCommand cmdInsert = null;
            SqlCommand cmdDelete = null;
            List<string> insertCmds = new List<string>();

            try
            {
                foreach (string projectName in foundProjects)
                {
                    queryString = String.Format(CultureInfo.InvariantCulture, "SELECT * FROM esQUEUE_ATTRIBUTE_EVENTS_ARCHIVE WHERE ProjectName = '{0}'", projectName);

                    _tracing.WriteTrace(_tracing.MainSwitch.TraceInfo, String.Format(CultureInfo.InvariantCulture, "Query String, {0}", queryString));
                    using (SqlConnection connection = new SqlConnection(PPSDBConnectionString))
                    {
                        SqlCommand command = new SqlCommand(queryString, connection);
                        connection.Open();

                        SqlDataReader reader = command.ExecuteReader();

                        while (reader.Read())
                        {
                            insertCmds.Add(String.Format(CultureInfo.InvariantCulture, "INSERT esQUEUE_ATTRIBUTE_EVENTS VALUES ({0},{1},{2},{3},'{4}','{5}','{6}')",
                                reader[1], reader[2], reader[3], reader[4],
                                reader[5], reader[6], reader[7]));
                        }
                        reader.Close();

                        foreach (string strInsert in insertCmds)
                        {
                            _tracing.WriteTrace(_tracing.MainSwitch.TraceInfo, String.Format(CultureInfo.InvariantCulture, "CMDInsert, {0}", strInsert));
                            cmdInsert = new SqlCommand(strInsert, connection);
                            cmdInsert.ExecuteNonQuery();
                            cmdInsert.Dispose();
                        }


                        string strDelete = String.Format(CultureInfo.InvariantCulture, "DELETE FROM esQUEUE_ATTRIBUTE_EVENTS_ARCHIVE WHERE ProjectName = '{0}'", projectName);
                        _tracing.WriteTrace(_tracing.MainSwitch.TraceInfo, String.Format(CultureInfo.InvariantCulture, "CMDInsert, {0}", strDelete));
                        cmdDelete = new SqlCommand(strDelete, _conn);
                        cmdDelete.ExecuteNonQuery();
                        cmdDelete.Dispose();
                    }
                }
            }
            catch (InvalidOperationException ioEx)
            {
                _tracing.WriteTrace(_tracing.MainSwitch.TraceError, String.Format(CultureInfo.InvariantCulture, "Connection already open, or invalid connection string.  Exception: {0}", ioEx.Message));
                successful = false;
            }
            catch (SqlException sqlEx)
            {
                _tracing.WriteTrace(_tracing.MainSwitch.TraceError, String.Format(CultureInfo.InvariantCulture, "A SQL exception occured connecting to the PPS database.  Exception: {0}", sqlEx.Message));
                successful = false;
            }
            catch (Exception ex)
            {
                _tracing.WriteTrace(_tracing.MainSwitch.TraceError, String.Format(CultureInfo.InvariantCulture, "Unexpected exception: {0}", ex.Message));
                successful = false;
            }

            return successful;
        }

       #endregion

        #region Change the Project DataSet with updates

        private bool UpdateField(ref ProjectWS.ProjectDataSet pds, AttributeChange ac)
        {
            Guid CFID = _config.GetCustomFieldGuid(ac.AttributeId);

            if (CFID != Guid.Empty)
            {
                // Found the matching Guid for the custom field, process as custom field
                if (!UpdateCustomField(ref pds, ac, CFID))
                {
                    // Custom Field not updated correctly
                    _tracing.WriteTrace(_tracing.MainSwitch.TraceError, String.Format(CultureInfo.InvariantCulture, "PS Custom Field ({0}) failed to update", CFID.ToString()));
                    return false;
                }
            }
            else
            {
                string builtinFieldName = _config.GetBuiltInFieldName(ac.AttributeId);
                //if (builtinFieldName != String.Empty)
                if (builtinFieldName != String.Empty)
                {
                    if (!UpdateBuiltinField(ref pds, ac, builtinFieldName))
                    {
                        // Field not updated correctly
                        _tracing.WriteTrace(_tracing.MainSwitch.TraceError, String.Format(CultureInfo.InvariantCulture, "Built in PS Field ({0}) failed to update", builtinFieldName));
                        return false;
                    }
                }
                else
                {
                    // Field is not mapped, could not find the name of the mapped field
                    _tracing.WriteTrace(_tracing.MainSwitch.TraceError, String.Format(CultureInfo.InvariantCulture,"PPS Field ({0}) mapped to unknown built-in field, update incomplete", ac.AttributeId));
                    return false;
                }
            }
            return true;
        }

        private static string getCurrencyCostOffset(ProjectWS.ProjectDataSet pds)
        {
            string offset = String.Empty;
            return offset.PadLeft(pds.Project[0].PROJ_OPT_CURRENCY_DIGITS, '0');
            //for (int i = 0; i <=  pds.Project[0].PROJ_OPT_CURRENCY_DIGITS- 1; i++)
            //{
            //    offset += "0";
            //}
            //return offset;
        }

        private bool UpdateCustomField(ref ProjectWS.ProjectDataSet pds, AttributeChange ac, Guid customFieldUid)
        {
            ProjectWS.ProjectDataSet.ProjectCustomFieldsRow cfRow;
            string currencyOffset = getCurrencyCostOffset(pds);

            for (int i = 0; i <= pds.ProjectCustomFields.Count - 1; i++)
            {
                if (customFieldUid == pds.ProjectCustomFields[i].MD_PROP_UID)
                {
                    try
                    {
                        if (!_projectCustomFieldList.CustomFieldHasLookupTable(customFieldUid))
                        {
                            // Found the row, we will update it.
                            switch (pds.ProjectCustomFields[i].FIELD_TYPE_ENUM)
                            {
                                case (byte)PSLibrary.CustomField.Type.DATE:
                                    pds.ProjectCustomFields[i].DATE_VALUE = Convert.ToDateTime(ac.NewValue, CultureInfo.InvariantCulture);
                                    break;
                                case (byte)PSLibrary.CustomField.Type.DURATION:
                                    pds.ProjectCustomFields[i].DUR_VALUE = Convert.ToInt32(ac.NewValue, CultureInfo.InvariantCulture);
                                    break;
                                case (byte)PSLibrary.CustomField.Type.FLAG:
                                    pds.ProjectCustomFields[i].FLAG_VALUE = Convert.ToBoolean(ac.NewValue, CultureInfo.InvariantCulture);
                                    break;
                                case (byte)PSLibrary.CustomField.Type.NUMBER:
                                    pds.ProjectCustomFields[i].NUM_VALUE = Convert.ToDecimal(ac.NewValue, CultureInfo.InvariantCulture);
                                    break;
                                case (byte)PSLibrary.CustomField.Type.COST:
                                    pds.ProjectCustomFields[i].NUM_VALUE = Convert.ToDecimal(ac.NewValue + currencyOffset, CultureInfo.InvariantCulture);
                                    break;
                                case (byte)PSLibrary.CustomField.Type.TEXT:
                                    pds.ProjectCustomFields[i].TEXT_VALUE = ac.NewValue;
                                    break;
                                case (byte)PSLibrary.CustomField.Type.FINISHDATE:
                                default:
                                    _tracing.WriteTrace(_tracing.MainSwitch.TraceError, String.Format(CultureInfo.InvariantCulture, "PS Field ({0}) failed to update correctly", customFieldUid));
                                    return false;
                            }
                        }
                        else
                        {
                            // Updating a code value
                            FieldMapping fm = _config.FieldMappings[_config.GetFieldMappingIndexFromAttributeID(ac.AttributeId)];
                            pds.ProjectCustomFields[i].CODE_VALUE = fm.GetPSGuidFromAttributeID(Convert.ToInt32(ac.NewValue, CultureInfo.InvariantCulture));
                        }
                    }
                    catch (Exception ex)
                    {
                        // Error updating the value
                        _tracing.WriteTrace(_tracing.MainSwitch.TraceError, String.Format(CultureInfo.InvariantCulture, "PS Field ({0}) failed to update correctly, exception ({1})", customFieldUid, ex.Message));
                        return false;
                    }
                    _tracing.WriteTrace(_tracing.MainSwitch.TraceInfo, String.Format(CultureInfo.InvariantCulture, "Attribute ID {0} processed for update, row edited", ac.AttributeId));
                    return true;
                }
            }

            try
            {
                // need to create a new row
                ProjectWS.ProjectDataSet.ProjectCustomFieldsRow newRow = pds.ProjectCustomFields.NewProjectCustomFieldsRow();

                newRow.CUSTOM_FIELD_UID = Guid.NewGuid();
                newRow.PROJ_UID = pds.Project[0].PROJ_UID;
                newRow.MD_PROP_UID = customFieldUid;

                cfRow = UpdateCustomFieldValue(newRow, customFieldUid, ac, currencyOffset);

                if (cfRow != null)
                {
                    pds.ProjectCustomFields.AddProjectCustomFieldsRow(newRow);
                    _tracing.WriteTrace(_tracing.MainSwitch.TraceInfo, String.Format(CultureInfo.InvariantCulture, "Attribute ID {0} processed for update, new row added", ac.AttributeId));
                    return true;
                }
                else
                {
                    _tracing.WriteTrace(_tracing.MainSwitch.TraceError, String.Format(CultureInfo.InvariantCulture, "PS Field ({0}) failed to update add new row", customFieldUid));
                    return false;
                }
            }
            catch (Exception ex)
            {
                // Error updating the value
                _tracing.WriteTrace(_tracing.MainSwitch.TraceError, String.Format(CultureInfo.InvariantCulture, "PS Field ({0}) failed to update add new row, exception ({1})", customFieldUid, ex.Message));
                return false;
            }
        }

        private ProjectWS.ProjectDataSet.ProjectCustomFieldsRow UpdateCustomFieldValue(ProjectWS.ProjectDataSet.ProjectCustomFieldsRow customFieldRow, Guid customFieldUid, AttributeChange changeRecord, string currencyOffset)
        {
            try
            {
                if (!_projectCustomFieldList.CustomFieldHasLookupTable(customFieldUid))
                {
                    // Found the row, we will update it.
                    switch (customFieldRow.FIELD_TYPE_ENUM)
                    {
                        case (byte)PSLibrary.CustomField.Type.DATE:
                            customFieldRow.DATE_VALUE = Convert.ToDateTime(changeRecord.NewValue, CultureInfo.InvariantCulture);
                            break;
                        case (byte)PSLibrary.CustomField.Type.DURATION:
                            customFieldRow.DUR_VALUE = Convert.ToInt32(changeRecord.NewValue, CultureInfo.InvariantCulture);
                            break;
                        case (byte)PSLibrary.CustomField.Type.FLAG:
                            customFieldRow.FLAG_VALUE = Convert.ToBoolean(changeRecord.NewValue, CultureInfo.InvariantCulture);
                            break;
                        case (byte)PSLibrary.CustomField.Type.NUMBER:
                            customFieldRow.NUM_VALUE = Convert.ToDecimal(changeRecord.NewValue, CultureInfo.InvariantCulture);
                            break;
                        case (byte)PSLibrary.CustomField.Type.COST:
                            customFieldRow.NUM_VALUE = Convert.ToDecimal(changeRecord.NewValue + currencyOffset, CultureInfo.InvariantCulture);
                            break;
                        case (byte)PSLibrary.CustomField.Type.TEXT:
                            customFieldRow.TEXT_VALUE = changeRecord.NewValue;
                            break;
                        case (byte)PSLibrary.CustomField.Type.FINISHDATE:
                        default:
                            _tracing.WriteTrace(_tracing.MainSwitch.TraceError, String.Format(CultureInfo.InvariantCulture, "PS Field ({0}) failed to update correctly", customFieldUid));
                            return null;
                    }
                }
                else
                {
                    // Updating a code value
                    FieldMapping fm = _config.FieldMappings[_config.GetFieldMappingIndexFromAttributeID(changeRecord.AttributeId)];
                    customFieldRow.CODE_VALUE = fm.GetPSGuidFromAttributeID(Convert.ToInt32(changeRecord.NewValue, CultureInfo.InvariantCulture));
                }
            }
            catch (Exception ex)
            {
                // Error updating the value
                _tracing.WriteTrace(_tracing.MainSwitch.TraceError, String.Format(CultureInfo.InvariantCulture, "PS Field ({0}) failed to update correctly, exception ({1})", customFieldUid, ex.Message));
                return null;
            }

            return customFieldRow;

        }

        private static bool UpdateBuiltinField(ref ProjectWS.ProjectDataSet pds, AttributeChange ac, string builtinFieldName)
        {
            switch (builtinFieldName)
            {
                case "PROJ_INFO_CURRENT_DATE":
                    pds.Project[0].PROJ_INFO_CURRENT_DATE = Convert.ToDateTime(ac.NewValue, CultureInfo.InvariantCulture);
                    break;
                case "PROJ_INFO_FINISH_DATE":
                    pds.Project[0].PROJ_INFO_FINISH_DATE = Convert.ToDateTime(ac.NewValue, CultureInfo.InvariantCulture);
                    break;
                case "PROJ_INFO_SCHED_FROM":
                    pds.Project[0].PROJ_INFO_SCHED_FROM = Convert.ToBoolean(ac.NewValue, CultureInfo.InvariantCulture);
                    break;
                case "PROJ_INFO_START_DATE":
                    pds.Project[0].PROJ_INFO_START_DATE = Convert.ToDateTime(ac.NewValue, CultureInfo.InvariantCulture);
                    break;
                case "PROJ_INFO_STATUS_DATE":
                    pds.Project[0].PROJ_INFO_STATUS_DATE = Convert.ToDateTime(ac.NewValue, CultureInfo.InvariantCulture);
                    break;
                case "PROJ_OPT_CALC_ACT_COSTS":
                    pds.Project[0].PROJ_OPT_CALC_ACT_COSTS = Convert.ToBoolean(ac.NewValue, CultureInfo.InvariantCulture);
                    break;
                case "PROJ_OPT_CRITICAL_SLACK_LIMIT":
                    pds.Project[0].PROJ_OPT_CRITICAL_SLACK_LIMIT = Convert.ToInt32(ac.NewValue, CultureInfo.InvariantCulture);
                    break;
                case "PROJ_OPT_DAYS_PER_MONTH":
                    pds.Project[0].PROJ_OPT_DAYS_PER_MONTH = Convert.ToInt16(ac.NewValue, CultureInfo.InvariantCulture);
                    break;
                case "PROJ_OPT_DEF_FIX_COST_ACCRUAL":
                    pds.Project[0].PROJ_OPT_DEF_FIX_COST_ACCRUAL = Convert.ToInt32(ac.NewValue, CultureInfo.InvariantCulture);
                    break;
                case "PROJ_OPT_DEF_OVT_RATE":
                    pds.Project[0].PROJ_OPT_DEF_OVT_RATE = Convert.ToDouble(ac.NewValue, CultureInfo.InvariantCulture);
                    break;
                case "PROJ_OPT_DEF_STD_RATE":
                    pds.Project[0].PROJ_OPT_DEF_STD_RATE = Convert.ToDouble(ac.NewValue, CultureInfo.InvariantCulture);
                    break;
                case "PROJ_OPT_DEF_TASK_TYPE":
                    pds.Project[0].PROJ_OPT_DEF_TASK_TYPE = Convert.ToInt32(ac.NewValue, CultureInfo.InvariantCulture);
                    break;
                case "PROJ_OPT_DUR_ENTRY_FMT":
                    pds.Project[0].PROJ_OPT_DUR_ENTRY_FMT = Convert.ToInt32(ac.NewValue, CultureInfo.InvariantCulture);
                    break;
                case "PROJ_OPT_FY_START_MONTH":
                    pds.Project[0].PROJ_OPT_FY_START_MONTH = Convert.ToInt16(ac.NewValue, CultureInfo.InvariantCulture);
                    break;
                case "PROJ_OPT_FY_USE_START_YR":
                    pds.Project[0].PROJ_OPT_FY_USE_START_YR = Convert.ToBoolean(ac.NewValue, CultureInfo.InvariantCulture);
                    break;
                case "PROJ_OPT_HONOR_CONSTRAINTS":
                    pds.Project[0].PROJ_OPT_HONOR_CONSTRAINTS = Convert.ToBoolean(ac.NewValue, CultureInfo.InvariantCulture);
                    break;
                case "PROJ_OPT_MINUTES_PER_DAY":
                    pds.Project[0].PROJ_OPT_MINUTES_PER_DAY = Convert.ToInt32(ac.NewValue, CultureInfo.InvariantCulture);
                    break;
                case "PROJ_OPT_MINUTES_PER_WEEK":
                    pds.Project[0].PROJ_OPT_MINUTES_PER_WEEK = Convert.ToInt32(ac.NewValue, CultureInfo.InvariantCulture);
                    break;
                case "PROJ_OPT_MOVE_ACTUAL_IF_LATER":
                    pds.Project[0].PROJ_OPT_MOVE_ACTUAL_IF_LATER = Convert.ToBoolean(ac.NewValue, CultureInfo.InvariantCulture);
                    break;
                case "PROJ_OPT_MOVE_ACTUAL_TO_STATUS":
                    pds.Project[0].PROJ_OPT_MOVE_ACTUAL_TO_STATUS = Convert.ToBoolean(ac.NewValue, CultureInfo.InvariantCulture);
                    break;
                case "PROJ_OPT_MOVE_REMAINING_IF_EARLIER":
                    pds.Project[0].PROJ_OPT_MOVE_REMAINING_IF_EARLIER = Convert.ToBoolean(ac.NewValue, CultureInfo.InvariantCulture);
                    break;
                case "PROJ_OPT_MOVE_REMAINING_TO_STATUS":
                    pds.Project[0].PROJ_OPT_MOVE_REMAINING_TO_STATUS = Convert.ToBoolean(ac.NewValue, CultureInfo.InvariantCulture);
                    break;
                case "PROJ_OPT_MULT_CRITICAL_PATHS":
                    pds.Project[0].PROJ_OPT_MULT_CRITICAL_PATHS = Convert.ToBoolean(ac.NewValue, CultureInfo.InvariantCulture);
                    break;
                case "PROJ_OPT_NEW_TASK_EST":
                    pds.Project[0].PROJ_OPT_NEW_TASK_EST = Convert.ToBoolean(ac.NewValue, CultureInfo.InvariantCulture);
                    break;
                case "PROJ_OPT_NEW_TASKS_ARE_EFFORT_DRIVEN":
                    pds.Project[0].PROJ_OPT_NEW_TASKS_ARE_EFFORT_DRIVEN = Convert.ToBoolean(ac.NewValue, CultureInfo.InvariantCulture);
                    break;
                case "PROJ_OPT_SHOW_EST_DUR":
                    pds.Project[0].PROJ_OPT_SHOW_EST_DUR = Convert.ToBoolean(ac.NewValue, CultureInfo.InvariantCulture);
                    break;
                case "PROJ_OPT_SPLIT_IN_PROGRESS":
                    pds.Project[0].PROJ_OPT_SPLIT_IN_PROGRESS = Convert.ToBoolean(ac.NewValue, CultureInfo.InvariantCulture);
                    break;
                case "PROJ_OPT_SPREAD_ACT_COSTS":
                    pds.Project[0].PROJ_OPT_SPREAD_ACT_COSTS = Convert.ToBoolean(ac.NewValue, CultureInfo.InvariantCulture);
                    break;
                case "PROJ_OPT_SPREAD_PCT_COMP":
                    pds.Project[0].PROJ_OPT_SPREAD_PCT_COMP = Convert.ToBoolean(ac.NewValue, CultureInfo.InvariantCulture);
                    break;
                case "PROJ_OPT_TASK_UPDATES_RES":
                    pds.Project[0].PROJ_OPT_TASK_UPDATES_RES = Convert.ToBoolean(ac.NewValue, CultureInfo.InvariantCulture);
                    break;
                case "PROJ_OPT_WEEK_START_DAY":
                    pds.Project[0].PROJ_OPT_WEEK_START_DAY = Convert.ToInt16(ac.NewValue, CultureInfo.InvariantCulture);
                    break;
                case "PROJ_OPT_WORK_ENTRY_FMT":
                    pds.Project[0].PROJ_OPT_WORK_ENTRY_FMT = Convert.ToInt32(ac.NewValue, CultureInfo.InvariantCulture);
                    break;
                case "PROJ_PROP_AUTHOR":
                    pds.Project[0].PROJ_PROP_AUTHOR = ac.NewValue;
                    break;
                case "PROJ_PROP_CATEGORY":
                    pds.Project[0].PROJ_PROP_CATEGORY = ac.NewValue;
                    break;
                case "PROJ_PROP_COMPANY":
                    pds.Project[0].PROJ_PROP_CATEGORY = ac.NewValue;
                    break;
                case "PROJ_PROP_KEYWORDS":
                    pds.Project[0].PROJ_PROP_COMPANY = ac.NewValue;
                    break;
                case "PROJ_PROP_MANAGER":
                    pds.Project[0].PROJ_PROP_MANAGER = ac.NewValue;
                    break;
                case "PROJ_PROP_SUBJECT":
                    pds.Project[0].PROJ_PROP_SUBJECT = ac.NewValue;
                    break;
                case "PROJ_PROP_TITLE":
                    pds.Project[0].PROJ_PROP_TITLE = ac.NewValue;
                    break;
                case "PROJ_PROTECTED_ACTUALS_SYNCH":
                    pds.Project[0].PROJ_PROTECTED_ACTUALS_SYNCH = Convert.ToBoolean(ac.NewValue, CultureInfo.InvariantCulture);
                    break;
                case "ProjectOwnerID":
                    pds.Project[0].ProjectOwnerID = new Guid(ac.NewValue);
                    break;
                case "WPROJ_DESCRIPTION":
                    pds.Project[0].WPROJ_DESCRIPTION = ac.NewValue;
                    break;
                case "WPROJ_TRACKING_METHOD":
                    pds.Project[0].WPROJ_TRACKING_METHOD = Convert.ToInt32(ac.NewValue, CultureInfo.InvariantCulture);
                    break;
                default:
                    return false;

            }

            return true;
        }

        #endregion

        #region Project Server Web Service Calls

        private bool UpdateProjectServerProject(Guid sessionUid, Guid projectUid, ProjectWS.ProjectDataSet projectDS)
        {
            bool updateProjectPassed = QueueUpdateProject(sessionUid, projectDS);

            bool checkInPassed = QueueCheckInProject(sessionUid, projectUid);

            bool publishPassed = true;
            if (_config.PublishAfterSave)
            {
                if (updateProjectPassed)
                    publishPassed = QueuePublish(projectUid);
                    //publishPassed = QueuePublish(SessionGuid, ProjectID);
            }

            if (updateProjectPassed && checkInPassed && publishPassed)
            {
                // Job completed correctly
                _attributeChanges.UpdateProjectState(projectUid, ChangeState.PSUpdateComplete);
                _tracing.WriteTrace(_tracing.MainSwitch.TraceInfo, String.Format(CultureInfo.InvariantCulture, "Project ID {0} updated correctly.", projectUid));
                return true;
            }
            else
            {
                // One of the jobs failed or timed out
                _attributeChanges.UpdateProjectState(projectUid, ChangeState.Error);
                _tracing.WriteTrace(_tracing.MainSwitch.TraceError, String.Format(CultureInfo.InvariantCulture, "Project ID {0} failed to update.", projectUid));
                return false;
            }
        }

        private bool QueuePublish(Guid projectUid)
        {
            bool publishPassed = false;
            
            try
            {
                Guid jobGuid = Guid.NewGuid();
                _projWebSvc.QueuePublish(jobGuid, projectUid, true, "");
                publishPassed = WaitForQueue(jobGuid);
            }
            catch (SoapException ex)
            {
                _tracing.WriteTrace(_tracing.MainSwitch.TraceError, String.Format(CultureInfo.InvariantCulture, "A SOAP exception occurred during QueuePublish: \r\n{0}", PSError.FormattedMessage(ex)));
                return false;
            }
            catch (WebException ex)
            {
                _tracing.WriteTrace(_tracing.MainSwitch.TraceError, String.Format(CultureInfo.InvariantCulture, "An WebException occurred during QueuePublish: \r\n{0}", ex.Message));
                return false;
            }
            catch (Exception ex)
            {
                _tracing.WriteTrace(_tracing.MainSwitch.TraceError, String.Format(CultureInfo.InvariantCulture, "An exception occurred during QueuePublish: \r\n{0}", ex.Message));
                return false;
            }

            if (publishPassed)
                _tracing.WriteTrace(_tracing.MainSwitch.TraceInfo, String.Format(CultureInfo.InvariantCulture, "Project [{0}] publish passed.", projectUid.ToString()));
            else
                _tracing.WriteTrace(_tracing.MainSwitch.TraceError, String.Format(CultureInfo.InvariantCulture, "Project [{0}] publish failed.", projectUid.ToString()));

            return publishPassed;
        }

        private bool QueueCheckInProject(Guid sessionUid, Guid projectUid)
        {
            bool checkInPassed = false;

            try
            {
                Guid jobGuid = Guid.NewGuid();
                // Force the check in, we don't want to leave a project checked out, it will never update then
                _projWebSvc.QueueCheckInProject(jobGuid, projectUid, true, sessionUid, "EPMSync.Service");
                checkInPassed = WaitForQueue(jobGuid);
            }
            catch (SoapException ex)
            {
                _tracing.WriteTrace(_tracing.MainSwitch.TraceError, String.Format(CultureInfo.InvariantCulture, "A SOAP exception occurred during QueueCheckInProject: \r\n{0}", PSError.FormattedMessage(ex)));
                return false;
            }
            catch (WebException ex)
            {
                _tracing.WriteTrace(_tracing.MainSwitch.TraceError, String.Format(CultureInfo.InvariantCulture, "An WebException occurred during QueueCheckInProject: \r\n{0}", ex.Message));
                return false;
            }
            catch (Exception ex)
            {
                _tracing.WriteTrace(_tracing.MainSwitch.TraceError, String.Format(CultureInfo.InvariantCulture, "An exception occurred during QueueCheckInProject: \r\n{0}", ex.Message));
                return false;
            }

            if (checkInPassed)
                _tracing.WriteTrace(_tracing.MainSwitch.TraceInfo, String.Format(CultureInfo.InvariantCulture, "Project [{0}] checked in.", projectUid.ToString()));
            else
                _tracing.WriteTrace(_tracing.MainSwitch.TraceError, String.Format(CultureInfo.InvariantCulture, "Project [{0}] check in failed.", projectUid.ToString()));

            return checkInPassed;
        }

        private bool QueueUpdateProject(Guid sessionUid, ProjectWS.ProjectDataSet projectDS)
        {
            bool queueUpdatePassed = false;

            try
            {
                Guid jobGuid = Guid.NewGuid();
                _projWebSvc.QueueUpdateProject(jobGuid, sessionUid, projectDS, false);
                queueUpdatePassed = WaitForQueue(jobGuid);
            }
            catch (SoapException ex)
            {
                _tracing.WriteTrace(_tracing.MainSwitch.TraceError, String.Format(CultureInfo.InvariantCulture, "A SOAP exception occurred during QueueUpdateProject: \r\n{0}", PSError.FormattedMessage(ex)));
                return false;
            }
            catch (WebException ex)
            {
                _tracing.WriteTrace(_tracing.MainSwitch.TraceError, String.Format(CultureInfo.InvariantCulture, "An WebException occurred during QueueUpdateProject: \r\n{0}", ex.Message));
                return false;
            }
            catch (Exception ex)
            {
                _tracing.WriteTrace(_tracing.MainSwitch.TraceError, String.Format(CultureInfo.InvariantCulture, "An exception occurred during QueueUpdateProject: \r\n{0}", ex.Message));
                return false;
            }

            if (queueUpdatePassed)
                _tracing.WriteTrace(_tracing.MainSwitch.TraceInfo, String.Format(CultureInfo.InvariantCulture, "Project [{0}] updated correctly.", projectDS.Project[0].PROJ_NAME));
            else
                _tracing.WriteTrace(_tracing.MainSwitch.TraceError, String.Format(CultureInfo.InvariantCulture, "Project [{0}] update failed.", projectDS.Project[0].PROJ_NAME));

            return queueUpdatePassed;
        }

        private ProjectWS.ProjectDataSet ReadProject(Guid projectUid, Guid sessionUid)
        {
            ProjectWS.ProjectDataSet pds;
            try
            {
                // TODO: Should this be working store or published store????
                pds = _projWebSvc.ReadProjectEntities(projectUid, 1057, ProjectWS.DataStoreEnum.WorkingStore);
                _projWebSvc.CheckOutProject(projectUid, sessionUid, "EPMSync.Service");
            }
            catch (SoapException ex)
            {
                _tracing.WriteTrace(_tracing.MainSwitch.TraceError, String.Format(CultureInfo.InvariantCulture, "A SOAP exception occurred during ReadProjectEntities or CheckOutProject: \r\n{0}", PSError.FormattedMessage(ex)));
                return null;
            }
            catch (WebException ex)
            {
                _tracing.WriteTrace(_tracing.MainSwitch.TraceError, String.Format(CultureInfo.InvariantCulture, "An WebException occurred during ReadProjectEntities or CheckOutProject: \r\n{0}", ex.Message));
                return null;
            }
            catch (Exception ex)
            {
                _tracing.WriteTrace(_tracing.MainSwitch.TraceError, String.Format(CultureInfo.InvariantCulture, "An exception occurred during ReadProjectEntities or CheckOutProject: \r\n{0}", ex.Message));
                return null;
            }

            _tracing.WriteTrace(_tracing.MainSwitch.TraceInfo, String.Format(CultureInfo.InvariantCulture, "Project [{0}] read and checked out correctly.", pds.Project[0].PROJ_NAME));
            return pds;
        }

        private bool WaitForQueue(Guid jobUid)
        {
            QueueSystemWS.JobState jobState;
            string xmlError = string.Empty;
            DateTime timeoutTime = DateTime.Now.AddSeconds(60);

            // Wait for the job to get through the queue
            int waitTime = _queueWebSvc.GetJobWaitTime(jobUid);
            if (waitTime > 10)
                waitTime = 10;
            Thread.Sleep(waitTime * 1000);

            do
            {
                // Get the job state
                jobState = _queueWebSvc.GetJobCompletionState(jobUid, out xmlError);

                if (jobState == QueueSystemWS.JobState.Success)
                {
                    _tracing.WriteTrace(_tracing.MainSwitch.TraceInfo, String.Format(CultureInfo.InvariantCulture, "Job ({0}) completed successfully", jobUid.ToString()));
                    return true;
                }
                else
                {
                    if (jobState == QueueSystemWS.JobState.Unknown
                    || jobState == QueueSystemWS.JobState.Failed
                    || jobState == QueueSystemWS.JobState.FailedNotBlocking
                    || jobState == QueueSystemWS.JobState.CorrelationBlocked
                    || jobState == QueueSystemWS.JobState.Canceled)
                    {
                        // If the job failed, error out
                        _tracing.WriteTrace(_tracing.MainSwitch.TraceError, String.Format(CultureInfo.InvariantCulture, "Job ({0}) failed with a state of ({1})", jobUid.ToString(), jobState.ToString()));
                        return false;
                    }
                    else
                    {
                        Thread.Sleep(1000);
                    }
                }

                if (DateTime.Now > timeoutTime)
                {
                    _tracing.WriteTrace(_tracing.MainSwitch.TraceWarning, String.Format(CultureInfo.InvariantCulture, "Job ({0}) failed to complete within specified timeout, there may be a problem", jobUid.ToString()));
                    return false;
                }

            }
            while (true);

        }
        #endregion

    }

}
